package nguyen.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;

import resource.pojo.BuyingDocument;
import resource.pojo.ListReference;

import model.pojo.Document;
import model.pojo.DocumentDetail;
import model.pojo.ListApprove;
import model.pojo.Re_ListApprove;
import models.util.MySqlDataAccessHelper;

public class ReceiverDocumentDAO {
	public ReceiverDocumentDAO() {

	}

	public static List<Document> getPendingDocument(String employeeID,
			String databasename, String lang) {
		
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		mysql.open(databasename);
		List<Document> list = new ArrayList<Document>();
		try {
			// String sql =
			// "SELECT approvedoc.* FROM approvedoc,listapprove WHERE  IDTypeDoc != 11  and listapprove.IDEmployee='"
			// + employeeID
			// + "'"
			// +
			// " AND listapprove.IDGroupApprove = approvedoc.IDGroupApprove AND (listapprove.IDStatus = 3 OR listapprove.IDStatus = 1) "
			// + "AND approvedoc.IDStatus=2";
			String sql = "SELECT approvedoc.*,dl.DepartmentName,e.FullName FROM approvedoc,listapprove,department d,department_lang dl,employee e "
					+ " WHERE  IDTypeDoc != 11  and listapprove.IDEmployee='"
					+ employeeID
					+ "'"
					+ " AND listapprove.IDGroupApprove = approvedoc.IDGroupApprove AND (listapprove.IDStatus = 3 OR listapprove.IDStatus = 1) "
					+ " AND approvedoc.IDStatus=2 and d.IDDepartment=e.IDDepartment and d.IDDepartment=dl.IDDepartment and e.IDEmployee='"
					+ employeeID
					+ "' "
					+ " and dl.LangID='"
					+ lang
					+ "' ORDER BY approvedoc.Timecreate desc";
			ResultSet rs = mysql.executeQuery(sql);
			Document doc = null;
			while (rs.next()) {
				doc = new Document(rs.getString("IDApproveDoc"),
						rs.getString("Tile"), rs.getString("Description"),
						rs.getString("Content"), rs.getString("Content2"),
						rs.getInt("IDTypeDoc"), rs.getTimestamp("Timecreate"),
						rs.getString("DepartmentName"),
						rs.getString("FullName"), rs.getString("Location"),
						rs.getString("EmployeePerforment"),
						rs.getTimestamp("TripDate"), rs.getString("TotalTime"),
						rs.getString("comment"));
				list.add(doc);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				mysql.close();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
		return list;
	}

	public static List<Document> getNotApprovedDocument(String employeeID,
			String databasename, String lang) {
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		mysql.open(databasename);
		List<Document> list = new ArrayList<Document>();
		try {
			// String sql =
			// "SELECT approvedoc.* FROM approvedoc,listapprove WHERE IDTypeDoc != 11 and listapprove.IDEmployee='"
			// + employeeID
			// + "'"
			// +
			// " AND listapprove.IDGroupApprove = approvedoc.IDGroupApprove AND listapprove.IDStatus=2 "
			// + " AND approvedoc.IDStatus=2";
			String sql = "SELECT approvedoc.*,dl.DepartmentName,e.FullName FROM approvedoc,listapprove,department d,department_lang dl,employee e "
					+ " WHERE IDTypeDoc != 11 and listapprove.IDEmployee='"
					+ employeeID
					+ "'"
					+ " AND listapprove.IDGroupApprove = approvedoc.IDGroupApprove AND listapprove.IDStatus=2 "
					+ " AND approvedoc.IDStatus=2 and d.IDDepartment=e.IDDepartment and d.IDDepartment=dl.IDDepartment and e.IDEmployee='"
					+ employeeID
					+ "' and dl.LangID='"
					+ lang
					+ "' ORDER BY approvedoc.Timecreate desc";
			ResultSet rs = mysql.executeQuery(sql);
			Document doc = null;
			while (rs.next()) {
				doc = new Document(rs.getString("IDApproveDoc"),
						rs.getString("Tile"), rs.getString("Description"),
						rs.getString("Content"), rs.getString("Content2"),
						rs.getString("IDEmployee"),
						rs.getString("IDGroupApprove"),
						rs.getTimestamp("Timecreate"), rs.getInt("IDTypeDoc"),
						rs.getString("DepartmentName"),
						rs.getString("FullName"), rs.getString("Location"),
						rs.getString("EmployeePerforment"),
						rs.getTimestamp("TripDate"), rs.getString("TotalTime"),
						rs.getString("comment"));
				list.add(doc);

			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				mysql.close();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
		return list;
	}

	public static String[] getDepartment_EmployeeName(String employeeID,
			String databasename, String lang) {
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		mysql.open(databasename);
		String department = "";
		String employeeName = "";
		try {
			String sql = "select dl.DepartmentName,e.FullName from department d,department_lang dl,employee e"
					+ " where d.IDDepartment=e.IDDepartment and d.IDDepartment=dl.IDDepartment"
					+ " and e.IDEmployee="
					+ employeeID
					+ " and dl.LangID='"
					+ lang + "'";
			ResultSet rs = mysql.executeQuery(sql);
			while (rs.next()) {
				department = rs.getString("DepartmentName");
				employeeName = rs.getString("FullName");
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				mysql.close();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
		return new String[] { department, employeeName };
	}

	@SuppressWarnings({ "rawtypes", "unchecked" })
	public static List getApprovedDocument(String employeeID,
			String databasename, int limit, int pageN, String lang) {
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		mysql.open(databasename);
		List map = new ArrayList();
		List<Document> list = new ArrayList<Document>();
		int count = 0;
		try {
			String sql = "SELECT count(approvedoc.IDApproveDoc) as countt FROM approvedoc,listapprove WHERE  IDTypeDoc != 11 and listapprove.IDEmployee='"
					+ employeeID
					+ "'"
					+ " AND listapprove.IDGroupApprove = approvedoc.IDGroupApprove AND listapprove.IDStatus=1 "
					+ " AND approvedoc.IDStatus=1 ";
			ResultSet rs = mysql.executeQuery(sql);
			while (rs.next()) {
				count = rs.getInt("countt");
				break;
			}
			rs.close();
			// sql =
			// "SELECT approvedoc.* FROM approvedoc,listapprove WHERE  IDTypeDoc != 11 and listapprove.IDEmployee='"
			// + employeeID
			// + "'"
			// +
			// " AND listapprove.IDGroupApprove = approvedoc.IDGroupApprove AND listapprove.IDStatus=1 "
			// + " AND approvedoc.IDStatus=1 limit "
			// + limit
			// * (pageN - 1)
			// + "," + limit;
			sql = "SELECT approvedoc.*,dl.DepartmentName,e.FullName FROM approvedoc,listapprove,department d,department_lang dl,employee e "
					+ " WHERE  IDTypeDoc != 11 and listapprove.IDEmployee='"
					+ employeeID
					+ "'"
					+ " AND listapprove.IDGroupApprove = approvedoc.IDGroupApprove AND listapprove.IDStatus=1 "
					+ " AND approvedoc.IDStatus=1 and d.IDDepartment=e.IDDepartment and d.IDDepartment=dl.IDDepartment	and e.IDEmployee='"
					+ employeeID
					+ "' and dl.LangID='"
					+ lang
					+ "' ORDER BY approvedoc.TimeApprove desc limit "
					+ limit
					* (pageN - 1) + "," + limit;
			rs = mysql.executeQuery(sql);
			Document doc = null;
			while (rs.next()) {
				doc = new Document(rs.getString("IDApproveDoc"),
						rs.getString("Tile"), rs.getString("Description"),
						rs.getString("Content"), rs.getString("Content2"),
						rs.getInt("IDTypeDoc"), rs.getTimestamp("Timecreate"),
						rs.getString("DepartmentName"),
						rs.getString("FullName"), rs.getString("Location"),
						rs.getString("EmployeePerforment"),
						rs.getTimestamp("TripDate"), rs.getString("TotalTime"),
						rs.getString("comment"));
				list.add(doc);
			}
			map.add(list);
			map.add(count);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				mysql.close();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
		return map;
	}

	public static List<Document> getDeclineDocument(String employeeID,
			String databasename, String lang) {
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		mysql.open(databasename);
		List<Document> list = new ArrayList<Document>();
		try {
			// String sql =
			// "SELECT approvedoc.* FROM approvedoc,listapprove WHERE IDTypeDoc != 11 and listapprove.IDEmployee='"
			// + employeeID
			// + "'"
			// +
			// " AND listapprove.IDGroupApprove = approvedoc.IDGroupApprove AND listapprove.IDStatus=3 "
			// + "AND approvedoc.IDStatus=3";
			String sql = "SELECT approvedoc.*,dl.DepartmentName,e.FullName FROM approvedoc,listapprove, department d,department_lang dl,employee e"
					+ " WHERE IDTypeDoc != 11 and listapprove.IDEmployee='"
					+ employeeID
					+ "'"
					+ " AND listapprove.IDGroupApprove = approvedoc.IDGroupApprove AND listapprove.IDStatus=3 "
					+ " AND approvedoc.IDStatus=3 and d.IDDepartment=e.IDDepartment and d.IDDepartment=dl.IDDepartment and e.IDEmployee='"
					+ employeeID
					+ "' and dl.LangID='"
					+ lang
					+ "' ORDER BY listapprove.DateApprove desc";

			ResultSet rs = mysql.executeQuery(sql);
			Document doc = null;
			while (rs.next()) {
				doc = new Document(rs.getString("IDApproveDoc"),
						rs.getString("Tile"), rs.getString("Description"),
						rs.getString("Content"), rs.getString("Content2"),
						rs.getInt("IDTypeDoc"), rs.getTimestamp("Timecreate"),
						rs.getString("DepartmentName"),
						rs.getString("FullName"), rs.getString("Location"),
						rs.getString("EmployeePerforment"),
						rs.getTimestamp("TripDate"), rs.getString("TotalTime"),
						rs.getString("comment"));
				list.add(doc);

			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				mysql.close();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
		return list;
	}

	public static List<Document> getMeetingPendingDocument(String employeeID,
			String databasename, String lang) {
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		mysql.open(databasename);
		List<Document> list = new ArrayList<Document>();
		try {
			// String sql =
			// "SELECT approvedoc.* FROM approvedoc,listapprove WHERE listapprove.IDEmployee='"
			// + employeeID
			// + "'"
			// +
			// " AND listapprove.IDGroupApprove = approvedoc.IDGroupApprove AND listapprove.IDStatus=2 "
			// + "AND approvedoc.IDStatus=2 And IDTypeDoc = 11";
			String sql = "SELECT approvedoc.*,dl.DepartmentName,e.FullName FROM approvedoc,listapprove, department d,department_lang dl,employee e"
					+ " WHERE listapprove.IDEmployee='"
					+ employeeID
					+ "'"
					+ " AND listapprove.IDGroupApprove = approvedoc.IDGroupApprove AND listapprove.IDStatus=2 "
					+ " AND approvedoc.IDStatus=2 And IDTypeDoc = 11 and d.IDDepartment=e.IDDepartment and d.IDDepartment=dl.IDDepartment and e.IDEmployee='"
					+ employeeID
					+ "' and dl.LangID='"
					+ lang
					+ "'  ORDER BY approvedoc.Timecreate desc";
			ResultSet rs = mysql.executeQuery(sql);
			Document doc = null;
			while (rs.next()) {
				doc = new Document(rs.getString("IDApproveDoc"),
						rs.getString("Tile"), rs.getString("Description"),
						rs.getString("Content"), rs.getTimestamp("Timecreate"),
						rs.getString("DepartmentName"),
						rs.getString("FullName"));
				list.add(doc);

			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				mysql.close();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
		return list;
	}

	public static List<Document> getReferenceDocument(String employeeID,
			String databasename, String lang) {

		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		mysql.open(databasename);
		List<Document> list = new ArrayList<Document>();
		try {
			// String sql =
			// "SELECT approvedoc.* FROM approvedoc,listapprove WHERE IDTypeDoc != 11 and listapprove.IDEmployee='"
			// + employeeID
			// + "'"
			// +
			// " AND listapprove.IDGroupApprove = approvedoc.IDGroupApprove AND listapprove.IDStatus = 5";

			String sql = "SELECT approvedoc.*,dl.DepartmentName,e.FullName FROM approvedoc,list_reference,department d,department_lang dl,employee e"
					+ " WHERE IDTypeDoc != 11 and list_reference.id_employee='"
					+ employeeID
					+ "'"
					+ " AND list_reference.id_group = approvedoc.IDGroupApprove AND list_reference.id_status = 2"
					+ " and d.IDDepartment=e.IDDepartment and d.IDDepartment=dl.IDDepartment and e.IDEmployee='"
					+ employeeID
					+ "'"
					+ " and dl.LangID='"
					+ lang
					+ "'  ORDER BY approvedoc.Timecreate desc";
			ResultSet rs = mysql.executeQuery(sql);
			Document doc = null;
			while (rs.next()) {
				doc = new Document(rs.getString("IDApproveDoc"),
						rs.getString("Tile"), rs.getString("Description"),
						rs.getString("Content"), rs.getString("Content2"),
						rs.getInt("IDTypeDoc"), rs.getTimestamp("Timecreate"),
						rs.getString("DepartmentName"),
						rs.getString("FullName"), rs.getString("Location"),
						rs.getString("EmployeePerforment"),
						rs.getTimestamp("TripDate"), rs.getString("TotalTime"),
						rs.getString("comment"));
				list.add(doc);

			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				mysql.close();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
		return list;
	}

	public static void acceptDocument(String employeeID, String employeeImage,
			String databasename, String getiDGroupApprove, String comment) {
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		int id = 0;
		mysql.open(databasename);
		try {
			String sql = "SELECT listapprove.Order FROM listapprove  WHERE IDEmployee = '"
					+ employeeID
					+ "' AND IDGroupApprove = '"
					+ getiDGroupApprove + "'";
			ResultSet rs = mysql.executeQuery(sql);
			while (rs.next()) {
				id = rs.getInt("listapprove.Order");
			}
			String sqlUpdate = "UPDATE listapprove SET IDStatus=1,DateApprove=NOW(),Description='"
					+ comment
					+ "',Signature='"
					+ employeeImage
					+ "' WHERE IDEmployee='"
					+ employeeID
					+ "' AND IDGroupApprove = '" + getiDGroupApprove + "'";
			mysql.executeUpdate(sqlUpdate);

			update(mysql, employeeID, getiDGroupApprove);
			updateDocument(mysql, getiDGroupApprove, id + 1);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				mysql.close();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
	}

	public static void updateDocument(MySqlDataAccessHelper mysql,
			String groupID, int id) {
		try {
			int pending = 0;
			String sql = "SELECT listapprove.IDStatus FROM listapprove,approvedoc "
					+ " WHERE listapprove.IDGroupApprove = approvedoc.IDGroupApprove"
					+ " AND listapprove.IDGroupApprove = '" + groupID + "'";
			ResultSet rs = mysql.executeQuery(sql);
			try {
				while (rs.next()) {
					if (rs.getInt("listapprove.IDStatus") == 4)
						pending++;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}

			if (pending == 0) {
				String sql1 = String
						.format("UPDATE approvedoc SET IDStatus=1 WHERE IDGroupApprove = '"
								+ groupID + "'");
				mysql.executeUpdate(sql1);
				// update date
				String sqlUpdateDate = String
						.format("UPDATE approvedoc SET TimeApprove = NOW() WHERE IDGroupApprove='%s'",
								groupID);
				mysql.executeUpdate(sqlUpdateDate);

			} else {
				String sql2 = "UPDATE listapprove SET IDStatus=2 WHERE listapprove.IDGroupApprove='"
						+ groupID + "' AND listapprove.Order=" + id;
				mysql.executeUpdate(sql2);
			}
		} catch (Exception e) {

		}
	}

	public static void update(MySqlDataAccessHelper mysql, String employeeID,
			String getiDGroupApprove) {

		String sqlCall = "CALL sp_updateReference(?,?)";
		CallableStatement cs;
		try {
			cs = mysql.getConnection().prepareCall(sqlCall);
			cs.setString(1, employeeID);
			cs.setString(2, getiDGroupApprove);

			cs.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	// .getString("listapprove.IDGroupApprove"), rs
	// .getString("listapprove.IDEmployee"), rs
	// .getInt("listapprove.IDtypeApprove"), rs
	// .getInt("listapprove.Order"), rs
	// .getInt("listapprove.IDStatus"), rs
	// .getTimestamp("listapprove.DateApprove"), rs
	// .getString("listapprove.Description"), rs
	// .getString("listapprove.Signature")));

	public static List getListApprover(String databasename,
			String iDApproveDoc, String lang) {
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		mysql.open(databasename);
		List array = new ArrayList(2);
		List<Re_ListApprove> list = new ArrayList<Re_ListApprove>();
		float total = 0.0f;
		try {
			String sql = "select lp.ID,em.FullName,dl.DepartmentName,sl."
					+ lang
					+ ",lp.DateApprove,(select SUM(bd.total) FROM buying_document bd WHERE bd.document_id='"
					+ iDApproveDoc
					+ "' ) as total "
					+ " from listapprove lp,approvedoc ap,`status` st,staticlang sl,employee em,department_lang dl "
					+ " where  lp.IDGroupApprove=ap.IDGroupApprove  and  st.IDStatus=lp.IDStatus "
					+ " and  st.IDConfigStaticLang=sl.IDConfigStaticLang "
					+ " and lp.IDEmployee=em.IDEmployee	and em.IDDepartment=dl.IDDepartment "
					+ " and dl.LangID='" + lang + "' and ap.IDApproveDoc="
					+ iDApproveDoc;

			ResultSet rs = mysql.executeQuery(sql);
			Re_ListApprove doc = null;
			while (rs.next()) {
				doc = new Re_ListApprove(rs.getInt("ID"),
						rs.getString("FullName"),
						rs.getString("DepartmentName"), rs.getString(lang),
						rs.getTimestamp("lp.DateApprove"));
				list.add(doc);
				total = rs.getFloat("total");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				mysql.close();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
		array.add(list);
		array.add(total);
		return array;
	}

	public static List<ListReference> getListReference(String employeeID,
			String databasename, String iDApproveDoc) {
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		mysql.open(databasename);
		List<ListReference> references = new ArrayList<ListReference>();
		try {
			String sql = "select list_reference.id, list_reference.id_employee, list_reference.id_manager, list_reference.id_group, list_reference.id_status"
					+ " from list_reference inner join approvedoc on list_reference.id_group=approvedoc.IDGroupApprove where approvedoc.IDApproveDoc="
					+ iDApproveDoc;
			ResultSet rs = mysql.executeQuery(sql);
			while (rs.next()) {
				references.add(new ListReference(rs
						.getString("list_reference.id_employee"), rs
						.getString("list_reference.id_manager"), rs
						.getString("list_reference.id_group"), rs
						.getInt("list_reference.id_status")));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				mysql.close();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
		return references;
	}

	public static List<ListApprove> rejectDocument(String employeeID,
			String databasename, String iDGroupApprove, String employeeImage,
			String comment) {
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		mysql.open(databasename);
		List<ListApprove> list = new ArrayList<ListApprove>();
		try {
			String sql = "UPDATE listapprove SET IDStatus=3,DateApprove=NOW(),Description='"
					+ comment
					+ "',Signature='"
					+ employeeImage
					+ "' WHERE IDEmployee='"
					+ employeeID
					+ "' AND IDGroupApprove = '" + iDGroupApprove + "'";
			mysql.executeUpdate(sql);

			String sql2 = "UPDATE approvedoc SET IDStatus=3 WHERE IDGroupApprove = '"
					+ iDGroupApprove + "'";
			mysql.executeUpdate(sql2);

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				mysql.close();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
		return list;
	}

	public static DocumentDetail getDocumentDetailForm12(String databasename,
			String iDApproveDoc) {
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		mysql.open(databasename);
		DocumentDetail doc_detail = null;
		try {
			String sql = "select ap.Timecreate,ap.Location,ap.EmployeePerforment,ap.TripDate,ap.TotalTime from approvedoc ap where ap.IDApproveDoc='"
					+ iDApproveDoc + "' limit 1";
			ResultSet rs = mysql.executeQuery(sql);
			while (rs.next()) {
				doc_detail = new DocumentDetail(rs.getTimestamp("Timecreate"),
						rs.getString("Location"),
						rs.getString("EmployeePerforment"),
						rs.getDate("TripDate"), rs.getString("TotalTime"));
				break;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				mysql.close();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
		return doc_detail;
	}

	public static DocumentDetail getDocumentDetailForm15(String databasename,
			String iDApproveDoc) {
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		mysql.open(databasename);
		DocumentDetail doc_detail = null;
		try {
			String sql = "select ap.Timecreate,ap.Location,ap.EmployeePerforment,ap.TripDate,ap.TotalTime,ap.`comment`,SUM(bd.total) as total "
					+ " from approvedoc ap,buying_document bd where ap.IDApproveDoc='"
					+ iDApproveDoc
					+ "' and bd.document_id="
					+ iDApproveDoc
					+ " limit 1";
			ResultSet rs = mysql.executeQuery(sql);
			while (rs.next()) {
				doc_detail = new DocumentDetail(rs.getTimestamp("Timecreate"),
						rs.getString("Location"),
						rs.getString("EmployeePerforment"),
						rs.getDate("TripDate"), rs.getString("TotalTime"),
						rs.getString("comment"), rs.getFloat("total"));
				break;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				mysql.close();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
		return doc_detail;
	}

	public static DocumentDetail getDocumentDetailForm13(String databasename,
			String iDApproveDoc) {
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		mysql.open(databasename);
		DocumentDetail doc_detail = null;
		try {
			String sql = "select ap.TripDate,ap.`comment`,SUM(bd.total) as total "
					+ " from approvedoc ap,buying_document bd where ap.IDApproveDoc='"
					+ iDApproveDoc
					+ "' and bd.document_id="
					+ iDApproveDoc
					+ " limit 1";
			ResultSet rs = mysql.executeQuery(sql);
			while (rs.next()) {
				doc_detail = new DocumentDetail(rs.getDate("TripDate"),
						rs.getString("comment"), rs.getFloat("total"));
				break;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				mysql.close();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
		return doc_detail;
	}

	public static List<BuyingDocument> getListProducts(String databasename,
			String iDApproveDoc) {
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		mysql.open(databasename);
		List<BuyingDocument> listProducts = new ArrayList<BuyingDocument>();
		try {
			String sql = "select * from buying_document where document_id = "
					+ iDApproveDoc;
			ResultSet rs = mysql.executeQuery(sql);
			while (rs.next()) {
				listProducts.add(new BuyingDocument(rs.getString("id"), rs
						.getString("document_id"), rs.getString("name"), rs
						.getString("company"), rs.getInt("amount"), rs
						.getString("unit"), rs.getFloat("price"), rs
						.getFloat("vat"), rs.getFloat("total")));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				mysql.close();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
		return listProducts;
	}

	// public static void main(String[] args) {
	// getListApprover("", "elihan", 498, "EN");
	// }

	// TODO hongmophi
	public int countAllNewMessage(String employeeID, String databasename) {
		open(databasename);
		int kq = -1;
		try {
			String sql = "SELECT count(approvedoc.IDApproveDoc) as count FROM approvedoc,listapprove WHERE IDTypeDoc != 11 and listapprove.IDEmployee='"
					+ employeeID
					+ "'"
					+ " AND listapprove.IDGroupApprove = approvedoc.IDGroupApprove AND listapprove.IDStatus=2 "
					+ " AND approvedoc.IDStatus=2";
			ResultSet rs = executeQuery(sql);
			if (rs.next()) {
				kq = rs.getInt("count");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				close();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
		return kq;
	}

	public List<String> countAllNewMessages(String employeeID,
			String databasename) {
		open(databasename);
		List<String> list = new ArrayList<String>();
		try {
			String sql = "SELECT approvedoc.IDApproveDoc FROM approvedoc,listapprove WHERE IDTypeDoc != 11 and listapprove.IDEmployee='"
					+ employeeID
					+ "'"
					+ " AND listapprove.IDGroupApprove = approvedoc.IDGroupApprove AND listapprove.IDStatus=2 "
					+ " AND approvedoc.IDStatus=2";
			ResultSet rs = executeQuery(sql);
			if (rs.next()) {
				list.add(rs.getString("IDApproveDoc"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				close();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
		return list;
	}

	private Connection connection;
	String userDB = "erp";
	String passDB = "nguyen";
	String host = "14.63.219.99";

	public Connection getConnection() {
		return connection;
	}

	private void open(String databasename) {
		try {
			DriverManager.registerDriver(new org.gjt.mm.mysql.Driver());
			String connectionString = "jdbc:mysql://" + host + ":3306/"
					+ databasename
					+ "?useUnicode=true&amp;characterEncoding=UTF-8";
			Properties pros = new Properties();
			pros.setProperty("characterEncoding", "utf8");
			pros.setProperty("user", userDB);
			pros.setProperty("password", passDB);

			this.connection = DriverManager.getConnection(connectionString,
					pros);

		} catch (SQLException ex) {
			ex.printStackTrace();
		}
	}

	private void close() {
		try {
			this.connection.close();
		} catch (SQLException ex) {
			Logger.getLogger(MySqlDataAccessHelper.class.getName()).log(
					Level.SEVERE, null, ex);
		}
	}

	public ResultSet executeQuery(String sql) {
		ResultSet rs = null;
		try {

			Statement sm = this.connection.createStatement();
			rs = sm.executeQuery(sql);
		} catch (SQLException ex) {
			System.out.println(ex.getMessage());
		}
		return rs;
	}

	public static float getTotalAmount(String databasename, String iDApproveDoc) {
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		mysql.open(databasename);
		float totalAmount = 0.0f;
		try {
			String sql = "select SUM(bd.total) as total "
					+ " from buying_document bd where bd.document_id="
					+ iDApproveDoc + " limit 1";
			ResultSet rs = mysql.executeQuery(sql);
			while (rs.next()) {
				totalAmount = rs.getFloat("total");
				break;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				mysql.close();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
		return totalAmount;
	}

	public static void referenceDoc(String databasename, String iDApproveDoc,
			List<ListReference> selects) {
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		mysql.open(databasename);
		try {
			String sql = "CALL sp_insertListReference(?,?,?,?,?)";
			CallableStatement cs = null;
			cs = mysql.getConnection().prepareCall(sql);
			for (ListReference l : selects) {
				cs.setString(1, new Date().getTime() + "");
				cs.setString(2, l.getId_emp());
				cs.setString(3, l.getId_mana());
				cs.setString(4, l.getId_group());
				cs.setInt(5, l.getIdStatus());
				cs.executeUpdate();
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				mysql.close();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
	}
	
	public static List<ListReference> getListReferenceDoc(String databasename, String idGroup
			) {
		List<ListReference> lst = new ArrayList<ListReference>();
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		mysql.open(databasename);
		try {
			String sql =  "CALL sp_getReferenceByGroup(?)";
			CallableStatement cs = null;
			cs = mysql.getConnection().prepareCall(sql);
			cs.setString(1, idGroup);
			
			ResultSet rs= cs.executeQuery();
			while(rs.next()){
				lst.add(new ListReference(rs.getString("id_employee"),
						rs.getString("id_manager"),
						rs.getString("id_group"),
						rs.getInt("id_status")));
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				mysql.close();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
		return lst;
	}
}
